package com.hns.can.master.component;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


import com.hns.can.master.entity.BaseDrvInfoEntity;
import com.hns.frame.enumrate.MsgType;
import com.hns.frame.exception.BusinessException;
import com.hns.tool.pub.PubUtil;

/**
 * @版权：HNS 版权所有 (c) 2016
 * @author:Chenl
 * @version Revision 2.0.0
 * @email:370238764@qq.com
 * @see:
 * @创建日期：2016-9-18
 * @功能说明：解析电子更纸excel文件成list对象
 * @begin
 * @修改记录:
 * @修改后版本          修改人      	修改内容
 * @2016-9-18 	         Chenl        	创建
 * @end
 */
public class ParseDriverInfoExcel {
    
    /**读取Exexl文件,转化成Execl
     * @param file
     * @return
     * @throws IOException
     * @author:Chenl
     * @email:370238764@qq.com
     * @创建日期:2016-9-21
     * @功能说明：提供对外访问的方法
     */
	
	
    //读取Execl
    public static List<BaseDrvInfoEntity> parseDriverInfoExcelToList(File file) throws Exception {
        Workbook wb = getWorkbook(file);
        List<BaseDrvInfoEntity> list = roadDriverInfoExecl(wb,file);
        return list;
    }
    
    /**
     * 读取表中全部的数据,存到实体类中,并添加到list里
     * @param wb
     * @return
     * @throws Exception 
     */
    //驾驶员信息模板
    public static List<BaseDrvInfoEntity> roadDriverInfoExecl(Workbook wb,File file) throws Exception{
    	Sheet sheet = null;
    	sheet = wb.getSheetAt(0);
    	if (!"驾驶员信息".equals(sheet.getSheetName())) {
            file.delete();
            return null;
        }
        Row row = null;
        Cell cell = null;
    	List<BaseDrvInfoEntity> list = new ArrayList<BaseDrvInfoEntity>();
    	SimpleDateFormat sqlRunTime = new SimpleDateFormat("yyyy-MM-dd");
		Date date = null;
			for (int i = 3;sheet.getRow(i)!=null; i++) {
				row = sheet.getRow(i);
				BaseDrvInfoEntity entity = new BaseDrvInfoEntity();
                int[] params = {0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17};
                for(int j : params){
                	cell = row.getCell(j);
                    String cellValue = getCellValue(cell).trim();
                    switch (j) {
                    	case 0:
                    		//驾驶员姓名
                    		if(PubUtil.isEmpty(cellValue)){
                    			break;
                    		}
                    		entity.setDrvName(cellValue);
	                		break;
                        case 1:
                            //考勤卡号
                            entity.setTimecardCardno(cellValue);
                            break;
                        case 2:
                            //考勤卡 背面号
                            entity.setTimecardCardnum(cellValue);
                            break;
                    	case 3:
                    		//员工工号
                    		entity.setWorkEmpeEmpno(cellValue);
                    		break;
                    	case 4:
                            //入职时间
                            entity.setEntryDate(cellValue);
	                		break;
                    	case 5:
                            //性别
                            if(PubUtil.isEmpty(cellValue)){
                                break;
                            }
                            entity.setGender(searchCode(cellValue));
	                        break;
                    	case 6:
                            //出生日期
                            entity.setBirthDate(cellValue);
                            break;
                    	case 7:
                            //联系电话
                            entity.setCtcWay(cellValue);
	                		break;
                    	case 8:
                            //籍贯
                            entity.setNtvplc(cellValue);
                            break;
                    	case 9:
                            //身份证号
                            entity.setIdcardNo(cellValue);
                            break;
                    	case 10:
                            //文化程度
                    		entity.setCultuDgr(searchCode(cellValue));
                            System.out.println(entity.getCultuDgr());
	                		break;
                    	case 11:
                            //联系地址
                    		entity.setCtcAdr(cellValue);
	                		break;
                    	case 12:
                            //初次领证日期
                            entity.setFirstLicensingTime(cellValue);
                            break;
                    	case 13:
                            //驾驶证日期
                            entity.setDriLicDate(cellValue);
	                		break;
                    	case 14:
                    	    //有效期限
                            entity.setValidityPeriod(cellValue);
	                		break;
                    	case 15:
                    		//驾驶证类型
                    		entity.setDriLicType(searchCode(cellValue));
                    		break;
                        case 16 :
                            //驾驶证发证机关
                            entity.setDrvLicenseOrgan(cellValue);
                            break;
                    }
                }
                if(PubUtil.isEmpty(entity.getDrvName()) && PubUtil.isEmpty(entity.getGender())){
                    break;
                }
                list.add(entity);
			}
		
    	return list;
    }


    public static String searchCode(String cellvalue){
        switch(cellvalue){
            case "小学":
                cellvalue = "DOE00001";
                break;
            case "中学":
                cellvalue = "DOE00002";
                break;
            case "中专":
                cellvalue = "DOE00003";
                break;
            case "大专":
                cellvalue = "DOE00004";
                break;
            case "本科":
                cellvalue = "DOE00005";
                break;
            case "硕士":
                cellvalue = "DOE00006";
                break;
            case "博士":
                cellvalue = "DOE00007";
                break;
            case "男":
                cellvalue = "SEX00001";
                break;
            case "女":
                cellvalue = "SEX00002";
                break;
            case "A1-大型客车":
                cellvalue = "DLT00001";
                break;
            case "A2-牵引车":
                cellvalue = "DLT00002";
                break;
            case "A3-城市公交车":
                cellvalue = "DLT00003";
                break;
            case "B1-中型客车":
                cellvalue = "DLT00004";
                break;
            case "B2-大型货车":
                cellvalue = "DLT00005";
                break;
        }
        return cellvalue;
    }
    
    /**
     * 读取Execl
     * @param file
     * @return
     * @author:Chenl
     * @email:370238764@qq.com
     * @throws FileNotFoundException
     * @throws IOException
     */
    public static Workbook getWorkbook(File file) throws FileNotFoundException, IOException{
    	 Workbook wb = null;
    	 String fileName = file.getName();
         String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName.substring(fileName.lastIndexOf(".") + 1);
         if ("xls".equals(extension)) { // 读取 office 2003 excel
             wb = new HSSFWorkbook(new FileInputStream(file));
         } else if ("xlsx".equals(extension)) { // 读取Office 2007 excel
             wb = new XSSFWorkbook(new FileInputStream(file));
         } else {
             file.delete();
             throw new BusinessException(MsgType.ERROR.getFlag(), "不支持的文件类型");
         }
         
         
         return wb;
    }
    
    /**
     * @param cell
     * @return
     * @author:JeffXu
     * @email:835264047@qq.com
     * @创建日期:2014-8-14
     * @功能说明：得到每个表格的值
     */
    private static String getCellValue(Cell cell) {
        String value = null;
        DecimalFormat df = new DecimalFormat("0");// 格式化 number String // 字符
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
        DecimalFormat nf = new DecimalFormat("#.##");// 格式化数字
        if(cell != null){
            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    value = cell.getStringCellValue();
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    if ("@".equals(cell.getCellStyle().getDataFormatString())) {
                        value = df.format(cell.getNumericCellValue());
                    } else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                        value = nf.format(cell.getNumericCellValue());
                    } else if (cell.getCellStyle().getDataFormat() == 22){
                        value = sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue()));
                    } else if(HSSFDateUtil.isCellDateFormatted(cell)){
                    		// 日期  
                    	if (cell.getCellStyle().getDataFormat() == 22) {  
                    		value = sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue()));
                        } else if(cell.getCellStyle().getDataFormat() == 21){
                        	sdf = new SimpleDateFormat("HH:mm:ss");
                        }else {
                            sdf = new SimpleDateFormat("yyyy-MM-dd");  
                        }    
                        Date date = cell.getDateCellValue();  
                        value =  sdf.format(date);
                    }else {
                    	double num = cell.getNumericCellValue();
                    	String temp = cell.getCellStyle().getDataFormatString();
                    	DecimalFormat format = new DecimalFormat();
                    	if (temp.equals("General")) {  
                            format.applyPattern("#");  
                        } 
                    	value = format.format(num);
                    }
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    value = String.valueOf(cell.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_BLANK:
                    value = "";
                    break;
                default:
                    value = cell.toString();
            }
        }else{
            value = "";
        }
        return value;
    }
    
    
    
//    public static void main(String[] args) throws IOException {
//        File file = new File("C:\\Users\\Administrator\\Desktop\\7月B21线电子更纸.xls");
//        List<EaiIttriplogEntity> list = parseExcelToList(file);
//        System.out.println("条数:"+list.size());
//        System.out.println(list);
//    }
}
